﻿using System;
using System.IO;
using System.Data;
using System.Data.OleDb;

namespace Ehai.Web.Framework.Rules
{
    class ExcelReadRuleHandle : IMsgRuleHandle
    {
        #region IMsgRuleHandle 成员

#if DEBUG

        private int _execStatus = 0;
        public int ExecStatus
        {
            get { return _execStatus; }
        }

#endif

        public const string Name = "excelread";


        #region 读取excel规则

        /// <summary>
        /// 读取excel指定行以下所有内容
        /// </summary>
        /// <param name="p">
        /// 0:  处理类型 excelread
        /// 1:  excel的绝对路径
        /// 2： sheet名(若为空，则默认为Sheet1$)
        /// 3： 从第几行开始读
        /// </param>
        /// <param name="ci">环境变量</param>
        /// <example>
        /// 示例代码
        /// </example>
        /// <code>
        /// string[][] r=Execute(new string[4]{"excelread","C:\\a.xls","Sheet1$","2"},ci);
        /// </code>
        /// <exception cref="Ehai.Web.Framework.WebException">
        /// Ehai.Web.Framework.WebException:读取Excel数据出错
        /// </exception>
        /// <returns>返回值的第一维是一行，第二维是一行当中的数据</returns>

        public string[][] Execute(string[] p, ContextInfo ci)
        {
            if (p.Length < 4)
            {

#if DEBUG
                _execStatus = -2;
#endif

                return null;
            }

            if (p[1] == null)
            {
#if DEBUG
                _execStatus = 1;
#endif

                return null;
            }

            if (p[2] == null)
            {
#if DEBUG
                _execStatus = 2;
#endif

                return null;
            }

            if (p[3] == null)
            {
#if DEBUG
                _execStatus = 3;
#endif

                return null;
            }

            p[2] = (p[2].Trim() == String.Empty) ? "Sheet1$" : p[2].Trim();
            p[3] = (p[3].Trim() == String.Empty) ? "0" : p[3];

            IDataReader reader = null;

            try
            {
                if (!File.Exists(p[1])) throw new Exception("excel文件不存在：" + p[1]);
                string xlsPath = p[1];
                string sheetName = p[2];
                int line = int.Parse(p[3]);
                String strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath + ";extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strCon);
                string strExcel = "select * from [" + sheetName + "]";
                OleDbCommand OleDbcmd = new OleDbCommand(strExcel, conn);
                conn.Open();
                reader = OleDbcmd.ExecuteReader(CommandBehavior.CloseConnection);

                string[][] result = null;
                System.Collections.Generic.List<string[]> tres = new System.Collections.Generic.List<string[]>();
                string[] temp = null;

                int l = 0;//行数
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        if (l >= line)
                        {

                            temp = new string[reader.FieldCount];
                            for (int i = 0; i < reader.FieldCount; i++)
                                temp[i] = reader[i].ToString();

                            tres.Add(temp);

                        }
                        l++;
                    }
                    result = new string[tres.Count][];
                    tres.CopyTo(result, 0);
                }
                else
                    result = new string[0][];

                if (reader != null)
                    reader.Close();

#if DEBUG
                _execStatus = 0;
#endif

                return result;

            }
            catch (Exception e)
            {

#if DEBUG
                _execStatus = -1;
#endif

                throw new Ehai.Web.Framework.WebException("Ehai.Web.Framework.Rules.ExcelRead", "读取Excel数据失败", e);
            }
        }
        #endregion

        public string GetHelpInfo()
        {
            return "读取excel指定行以下所有内容";
        }

        public string GetHelpDetail()
        {
            return "读取excel指定行以下所有内容.参数范例：{excelread,C:/a.xls,Sheet1$,2}";
        }

        public string[] GetParametersInfo()
        {

            string[] t = new string[4];
            t[0] = "[0]操作类型 excelread";
            t[1] = "[1]excel的绝对路径";
            t[2] = "[2]sheet名";
            t[3] = "[3]从第几行开始读";
            return t;
        }

        #endregion
    }
}
